package com.apestech.api.query.sys;

import com.apestech.api.query.XLSQuery;
import com.apestech.api.query.mapping.EQMapping;
import com.apestech.api.query.sys.erp.ErpRoleSql;
import com.apestech.api.query.sys.esb.JPARoleSql;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.dictionary.DataDictionary;
import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.condition.FieldSql;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.web.lib.ResponseWriter;
import com.apestech.framework.xml.plugin.modul.QryConfig;
import com.apestech.framework.xml.sql.SqlNodeVar;
import javax.servlet.http.HttpServletResponse;
import java.sql.SQLException;

/**
 * @author hup
 * @create 2018-11-27-11:47
 */
public class SysSql {

    private static JSONObject postJSON=new JSONObject();

    public static JSONObject getRole(String sysId, JSONArray list, String postId, JSONObject input) throws Exception {
        if(postId==null) return null; //postId="A0385";
        if(list==null) return null;
        if(sysId==null && list!=null && list.get(0)!=null){
            sysId=(list.get(0)).getString("sysId");
        }
        if(sysId==null) {
            return getEsbRole(list, postId);
        }
        switch (sysId){
            case "ESB":{
                return getEsbRole(list, postId);
            }
            case "ERP":{
                return getErpRole(list, postId, input);
            }
        }
        return null;
    }

    private static JSONObject getErpRole(JSONArray list, String postId, JSONObject input) throws Exception {
        JSONObject json=new JSONObject();
        int len=list.size();
        for(int i=0; i<len; i++){
            JSONObject m=JSON.toJSON(list.get(i));
            String exp=m.getString("exp");
            String field=m.getString("field");
            String type=m.getString("type");
            String purpose=m.getString("purpose");
            if(purpose==null){
                if(input.get("F_CXQX")!=null) purpose="pp_search";
                else if(input.get("F_CZQX")!=null) purpose="pp_operate";
            }
            if(field==null || "".equals(field)) throw new Exception("权限参数[field]不允许为空！");
            if(type==null || "".equals(type)) throw new Exception("权限参数[type]不允许为空！");
            if(purpose==null || "".equals(purpose)) throw new Exception("权限参数[purpose]不允许为空！");
            if(exp==null) exp=field;
            m.put("exp",exp);
            ErpRoleSql erp=new ErpRoleSql(postId, type, purpose);
            erp.setInput(input);
            FieldSql sql=new FieldSql(field, erp);
            m.put("sql", sql);
            m.remove("field");
            m.remove("operator");
            m.remove("type");
            m.remove("purpose");
            list.set(i, m);
            json.put(exp, SqlNodeVar.getInnerValue());
        }
        json.put("auth", list);
        return json;
    }

    private static JSONObject getEsbRole(JSONArray list, String postId) throws Exception {
        JSONObject json=new JSONObject();
        int len=list.size();
        for(int i=0; i<len; i++){
            JSONObject m=JSON.toJSON(list.get(i));
            String exp=m.getString("exp");
            String field=m.getString("field");
            String type=m.getString("type");
            String purpose=m.getString("purpose");
            if(field==null || "".equals(field)) throw new Exception("权限参数[field]不允许为空！");
            if(type==null || "".equals(type)) throw new Exception("权限参数[type]不允许为空！");
            if(purpose==null || "".equals(purpose)) throw new Exception("权限参数[purpose]不允许为空！");
            if(exp==null) exp=field;
            m.put("exp",exp);
            FieldSql sql=new FieldSql(field, new JPARoleSql(postId, type, purpose));
            m.put("sql", sql);
            m.remove("field");
            m.remove("operator");
            m.remove("type");
            m.remove("purpose");
            list.set(i, m);
            json.put(exp, SqlNodeVar.getInnerValue());
        }
        json.put("auth", list);
        return json;
    }

    public static void query(JSONObject json, HttpServletResponse response, SqlSession session, QryConfig config) throws Exception {
        final ResponseWriter[] writer = {null};
        try {
            session.setQryConfig(config);
            if(config!=null && "yyerp".equals(config.getQryConType())){
                EQMapping eq=new EQMapping(json);
                eq.expain();
                json=eq.getResult();
            }
            QueryCallBackHandler callback=new QueryCallBackHandler(){
                @Override
                public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                    writer[0] =new ResponseWriter(response);
                    if(config==null) writer[0].setSysid("ESB");
                    else{
                        if(config.getOutputWeb()==null) writer[0].setSysid("ESB");
                        else writer[0].setSysid(config.getOutputWeb());
                    }
                    JSONObject param=new JSONObject();
                    if(rs!=null){
                        long esize=rs.getConParam().getSize();
                        if(rs.getConParam().getPage()==rs.getConParam().getTotalPage()){
                            long rows=rs.getConParam().getPage()*rs.getConParam().getSize();
                            if(rs.getConParam().getTotal()>rows){
                                esize=rs.getConParam().getTotal()-rows;
                            } else {
                                esize=rs.getConParam().getTotal();
                            }
                        }
                        if(config!=null && "ERP".equals(config.getOutputWeb())){
                            param.put("pagetotal", rs.getConParam().getTotalPage());
                            param.put("prow", esize);
                            param.put("pretotal", rs.getConParam().getTotal());
                            param.put("page", rs.getConParam().getPage());
                            param.put("rows", rs.getConParam().getSize());
                        } else {
                            param.put("pages", rs.getConParam().getTotalPage());
                            param.put("page", rs.getConParam().getPage()-1);
                            param.put("size", rs.getConParam().getSize());
                            param.put("elements", esize);
                        }
                        param.put("total", rs.getConParam().getTotal());
                        param.put("gridheader", rs.getConParam().getHead());
                    } else if(rslist!=null){
                        if(rslist.size()>0 && rslist.get(0).get("SYS_PAGE")!=null) {
                            JSONObject m= (JSONObject) rslist.get(0).get("SYS_PAGE");
                            param.put("page", m.get("Page"));
                            param.put("elements", m.get("Size"));
                            param.put("size", m.get("Size"));
                            param.put("pages", m.get("TotalPage"));
                            param.put("total", m.get("Total"));
                            if(param.get("page")!=null){
                                param.put("page", param.getIntValue("page")-1);
                            }
                            rslist.remove(0);
                        }
                    }
                    writer[0].writeJSONDetail("true", "成功",param, rs, rslist);
                }
            };
            String queryId=json.getString("queryid");
            if(queryId!=null) session.getForResultSet().queryId(queryId, json, callback);
            else {
                String sql=json.getString("sql");
                if(sql!=null){
                    session.getForResultSet().sql(sql, json, callback);
                } else {
                    String table = json.getString("table");
                    if (table != null) session.getForResultSet().table(table, json, callback);
                    else {
                        String sysTable=json.getString("sysTable");
                        if(sysTable!=null){
                            callback.getData(null, (JSONArray) json.get("sysQueryList"));
                        }
                    }
                }
            }
        } catch (Exception e) {
            if(writer[0] !=null){
                try {
                    writer[0].writeError("writeWeb", "WEB写数失败");
                } catch (Exception e1) {
                    throw new RuntimeException(e);
                }
            }
            throw new RuntimeException(e);
        }
    }

    public static void queryXls(JSONObject json, HttpServletResponse response, SqlSession session, QryConfig config) throws Exception {
        final ResponseWriter[] writer = {null};
        try {
            String queryId=json.getString("queryid");
            session.setQryConfig(config);
            if(config!=null && "yyerp".equals(config.getQryConType())){
                EQMapping eq=new EQMapping(json);
                eq.expain();
                json=eq.getResult();
            }
            json.put("nb_xls", "1");
            JSONObject finalJson = json;
            QueryCallBackHandler callback=new QueryCallBackHandler(){
                @Override
                public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                    writer[0] =new ResponseWriter(response);
                    XLSQuery xls=new XLSQuery(rs);
                    String xlsname=finalJson.getString("xlsName");
                    if(xlsname==null) xlsname=rs.getConParam().getSingle().getString("queryname");
                    if(xlsname==null) xlsname=StringUtil.getUUID();
                    //xlsname=xlsname+ StringUtil.getUUID();
                    xls.setXlsName(xlsname);
                    xls.setSheetName(finalJson.getString("sheetName"));
                    xls.setMaxXlsRow(6000);  //生成的单个xls中写sheet页数＝MaxSheetRow * 想要显示的sheet页数
                    xls.setMaxSheetRow(6000);  //可不配，默认为一页显示20000
                    xls.writeXls();
                    writer[0].downFile(xls.getOutFile());
                    xls.close();
                }
            };
            if(queryId!=null){
                session.getForResultSet().queryId(queryId, json, callback);
            } else {
                session.getForResultSet().sql(json.getString("sql"), json, callback);
            }
        } catch (Exception e) {
            if(writer[0] !=null){
                try {
                    writer[0].writeError("writeWeb", "WEB写数失败");
                } catch (Exception e1) {
                    throw new RuntimeException(e);
                }
            }
            throw new RuntimeException(e);
        }
    }

    public static JSONObject queryXls(JSONObject json, SqlSession session, QryConfig config) throws Exception {
        JSONObject result=new JSONObject();
        try {
            String queryId=json.getString("queryid");
            session.setQryConfig(config);
            if(config!=null && "yyerp".equals(config.getQryConType())){
                EQMapping eq=new EQMapping(json);
                eq.expain();
                json=eq.getResult();
            }
            json.put("nb_xls", "1");
            JSONObject finalJson = json;
            QueryCallBackHandler callback=new QueryCallBackHandler(){
                @Override
                public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                    XLSQuery xls=new XLSQuery(rs);
                    String xlsname=finalJson.getString("xlsName");
                    if(xlsname==null) xlsname=rs.getConParam().getSingle().getString("queryname");
                    if(xlsname==null) xlsname=StringUtil.getUUID();
                    //xlsname=xlsname+ StringUtil.getUUID();
                    xls.setXlsName(xlsname);
                    xls.setSheetName(finalJson.getString("sheetName"));
                    xls.setMaxXlsRow(60000000);  //生成的单个xls中写sheet页数＝MaxSheetRow * 想要显示的sheet页数
                    xls.setMaxSheetRow(1000000);  //可不配，默认为一页显示20000
                    xls.writeXls();
                    result.put("file", xls.getOutFile());
                    xls.close();
                }
            };
            if(queryId!=null){
                session.getForResultSet().queryId(queryId, json, callback);
            } else {
                session.getForResultSet().sql(json.getString("sql"), json, callback);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    public static JSONObject getConditionRoleSql(String postId, SqlSession session) throws Exception {
        if(postJSON.get(postId)==null){
            JSONObject result=new JSONObject();
            result.put("postId", postId);
            result.put("autoCompletion", "0");
            String sql="SELECT ROLE3.PURPOSE,TO_CHAR(WM_CONCAT(ROLE3.TYPE)) TYPE  FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, AUT_LOCAL_PERMIT_ITEM ROLE3\n" +
                    "WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID AND ROLE1.ID=#{postId} " +
                    "GROUP BY ROLE3.PURPOSE";
            session.getForResultSet().sql(sql, result, new QueryCallBackHandler() {
                @Override
                public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                    while(rs.next()){
                        result.put("type", rs.getString("TYPE"));
                        result.put("purpose", rs.getString("PURPOSE"));
                    }
                }
            });
            postJSON.put(postId, result);
        }
        return (JSONObject) postJSON.get(postId);
    }

    public static String getTableSql(JSONObject json, SqlSession session) throws Exception {
        String tab=json.getString("table");
        String sqltype=json.getString("sqltype");
        if(tab==null){
            throw new Exception("table名不能为空！");
        }
        if(sqltype==null){
            throw new Exception("sqltype类型必填：select, insert, update, delete！");
        }
        tab=tab.toUpperCase();
        if(!session.tableExists(tab)){
            throw new Exception("表["+tab+"]不存在，请检查！");
        }
        DataDictionary.refreshTable(session, tab);
        switch (sqltype){
            case "select":{
                return session.getForResultSet().tableSelectSql(tab);
            }
            case "insert":{
                return session.getForResultSet().tableInsertSql(tab);
            }
            case "update":{
                return session.getForResultSet().tableUpdateSql(tab);
            }
            case "delete":{
                return session.getForResultSet().tableDeleteSql(tab);
            }
            default:return "";
        }

    }



}
